    using System;
    using System.Data;
    using System.Data.OleDb;
    using System.Data.Common;
    using System.Reflection;
    using System.Configuration;
    using System.Collections.Generic;
using PersistentManager.Provider;
using PersistentManager.Query.Sql;
using PersistentManager.Query;
using PersistentManager.Descriptors;
using System.Text;
using System.Linq;

namespace PersistentManager
{
    internal class OleDbProvider : DatabaseProvider
    {
        internal OleDbProvider( )
            : base( null , null )
        {

        }

        internal OleDbProvider( IDbTransaction transaction , IDbConnection connection )
            : base( connection , transaction )
        {

        }

        public override SQLTokenizer GetFilteredQuerySyntax( SQLTokenizer Tokens , int index )
        {
            Tokens.AddFormatted( QueryPart.SELECT_HEADER , string.Format( " TOP {0} " , index ) );

            return Tokens;
        }

        public override int ExecuteUpdate( string query , ref DbParameter returnedValue , string columnName , List<DbParameter> parameterCollection )
        {
            int update = ExecuteUpdate( query , parameterCollection );
            returnedValue.Value = GetAutoGeneratedColumnValue( );

            return update;
        }

        public override string GetNamingStrategyString( )
        {
            return "[{0}]";
        }

        public override string GetParameterPrefix( )
        {
            return "@";
        }

        public object GetAutoGeneratedColumnValue( )
        {
            string query = "select @@IDENTITY;\n";
            return ExecuteScalar( query );
        }

        public override void AddParameters( IDbCommand command , List<DbParameter> parameterCollection )
        {
            foreach ( OleDbParameter parameter in parameterCollection )
            {
                parameter.OleDbType = GetParameterType( parameter );
                command.Parameters.Add( parameter );
            }
        }

        private OleDbType GetParameterType( OleDbParameter parameter )
        {
            switch ( parameter.DbType )
            {
                case DbType.Binary:
                    return OleDbType.Binary;
                case DbType.Boolean:
                    return OleDbType.Boolean;
                case DbType.Currency:
                    return OleDbType.Currency;
                case DbType.DateTime:
                    return OleDbType.Date;
                case DbType.Int32:
                    return OleDbType.Integer;
                case DbType.Int64:
                    return OleDbType.BigInt;
                case DbType.String:
                    return OleDbType.WChar;
                default:
                    return parameter.OleDbType;
            }
        }

        public override DbDataReader Range( SQLTokenizer tokens , int StartRange , int Endrange , string query , List<DbParameter> parameters )
        {
            string ALIAS = tokens.EntityALIAS;
            string schema = tokens.EntityMetadata.SchemaName;

            string orderDirection = tokens.OrderByDirection().IsNullOrEmpty() ? " ASC " : tokens.OrderByDirection();
            int pageSize = (Endrange - StartRange) == 0 ? 1 : (Endrange - StartRange);

            StringBuilder RangeBuilder = new StringBuilder();
            RangeBuilder.Append(" SELECT outerx.* FROM ( ");

            tokens.Add(QueryPart.SELECT_HEADER, string.Format(" Top {0} ", Endrange));

            if (tokens.HasOrderBy)
            {
                RangeBuilder.AppendFormat("SELECT TOP {0} {1} FROM ( ", pageSize, "mainx.*");
                tokens.CopyFrom(QueryPart.ORDERBY, QueryPart.SELECT, true);
            }
            else if (!tokens.HasGroupBy)
            {
                tokens.Add(tokens.EntityMetadata.Keys, ALIAS, QueryPart.ORDERBY);
                tokens.Add(tokens.EntityMetadata.Keys, ALIAS, QueryPart.SELECT);

                RangeBuilder.AppendFormat("SELECT TOP {0} {1} FROM ( ", pageSize, "mainx.*");
            }
            else if (tokens.HasGroupBy)
            {
                RangeBuilder.AppendFormat("SELECT TOP {0} {1} FROM ( ", pageSize, "mainx.*");
                tokens.CopyFrom(QueryPart.GroupBy, QueryPart.SELECT, true);
                tokens.CopyFrom(QueryPart.GroupBy, QueryPart.ORDERBY, false);
            }

            RangeBuilder.AppendFormat(tokens.ToString());
            RangeBuilder.AppendFormat(") mainx ");
            RangeBuilder.AppendFormat(Dialect.OrderByMethod(tokens.GetOrderBys("mainx"), orderDirection.Contains("ASC") ? " DESC " : " ASC "));
            RangeBuilder.AppendFormat(") outerx ");
            RangeBuilder.AppendFormat(" ORDER BY {0} {1} ", tokens.GetOrderBys("outerx").ElementsToString(","), orderDirection);

            return GetDataReader(RangeBuilder.ToString(), parameters);
        }

        public override DbParameter GetCommandParameter( string name , object value )
        {
            return new System.Data.OleDb.OleDbParameter( name , value );
        }
    }
}
